package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Pages;
public class PagesMod {
	Connection connection; 
	public static PagesMod isntancePM;
	public static PagesMod getIsntance(){
		if(isntancePM == null){
			isntancePM = new PagesMod();
		}
		return isntancePM;
	}
	public boolean isPid(int pageid){
		boolean result = false;
		connection = DB_Connection.getIsntance().getConnection();
		try {
			String sql = "SELECT * FROM pages WHERE pageid = ? ";
			PreparedStatement sqli = connection.prepareStatement(sql);
			sqli.setInt(1, pageid);
			int n = sqli.executeUpdate();
			if(n > 0) result = true ;
			sqli.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean add(Pages p) throws SQLException {
		boolean result = false;
		connection = DB_Connection.getIsntance().getConnection();
		PreparedStatement sqli = null;
		String sql = "INSERT INTO pages( uid, page_name, content, position, post_on) VALUES ( ? , ? , ? , ? ,NOW() )";
		sqli = connection.prepareStatement(sql);
		sqli.setInt(1, p.getUid());
		sqli.setString(2, p.getPage_name());
		sqli.setString(3, p.getContent());
		sqli.setString(4, p.getPosition());
		int n = sqli.executeUpdate();
		if (n > 0)
			result = true;
		sqli.close();
		connection.close();
		return result;
	}
	public Pages getSMS(int id){
		Pages result = null;
		connection = DB_Connection.getIsntance().getConnection();
		try {
			String sql = "SELECT * FROM pages WHERE pageid = ? ";
			PreparedStatement sqli = connection.prepareStatement(sql);
			sqli.setInt(1, id);
			ResultSet rs = sqli.executeQuery();
			while(rs.next()){
				result = new Pages(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getDate(6));
			}
			sqli.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<Pages> getSMSByPosition(String p){
		 ArrayList<Pages> result = null;
		connection = DB_Connection.getIsntance().getConnection();
		try {
			String sql = "SELECT * FROM pages WHERE position = ? ";
			PreparedStatement sqli = connection.prepareStatement(sql);
			sqli.setString(1, p);
			ResultSet rs = sqli.executeQuery();
			while(rs.next()){
				result.add( new Pages(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getDate(6)));
			}
			sqli.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<Pages> getSMSByUid(int uid){
		 ArrayList<Pages> result = null;
		connection = DB_Connection.getIsntance().getConnection();
		try {
			String sql = "SELECT * FROM pages WHERE uid = ? ";
			PreparedStatement sqli = connection.prepareStatement(sql);
			sqli.setInt(1, uid);
			ResultSet rs = sqli.executeQuery();
			while(rs.next()){
				result.add( new Pages(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getDate(6)));
			}
			sqli.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean delByUid(int id){
		PreparedStatement delpd = null;
		boolean result = false;
		Connection connection = DB_Connection.getIsntance().getConnection();
		try {
			delpd = connection.prepareStatement("DELETE FROM pages WHERE uid = ?");
			delpd.setInt(1, id);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}
			delpd.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return  result;
	}
	public boolean del(int id){
		PreparedStatement delpd = null;
		boolean result = false;
		Connection connection = DB_Connection.getIsntance().getConnection();
		try {
			delpd = connection.prepareStatement("DELETE FROM pages WHERE pageid = ? LIMIT = 1");
			delpd.setInt(1, id);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}
			delpd.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return  result;
	}
}
